from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from queue import Queue
from pyppeteer import launch
from pyppeteer.browser import Browser
from pyppeteer.network_manager import Response
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from ftplib import FTP
from mini_tools.ftpLoad import downloadImg
from sql import sqlseverDB
import tkinter.messagebox as messagebox
import openpyxl,re,asyncio,concurrent.futures


class HomeFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='采集区', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)
        CJSkuFrame(self).pack(pady=5,fill=X,padx=5)
        PiePeiFrame(self).pack(pady=5,fill=X,padx=5)

class CJSkuFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statusStr=StringVar()
        self.statusStr.set('请先输入商家编码查询任务状态')
        self.createWidgets()
        self.t0=Thread(target=self.getcount)

    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='采集店铺库存', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,side=LEFT,padx=2,pady=2)
        f000=Frame(f00)
        f000.pack(fill=X)
        Label(f000, text='商家编码:').pack(side=LEFT, padx=2, pady=2)
        self.SJBMIpt = Entry(f000,width=10)
        self.SJBMIpt.pack(side=LEFT,padx=2, pady=2)
        self.btn_catstatus=Button(f000,text='查询任务状态',command=lambda:Thread(target=self.cat_skutask).start(),fg='#ffffff',bg='green')
        self.btn_catstatus.pack(side=LEFT, padx=2, pady=2)
        self.btn_cjsku=Button(f000,text='发布采集任务',command=lambda:Thread(target=self.send_task).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_cjsku.pack(side=LEFT, padx=2, pady=2)
        Label(f000, textvariable=self.statusStr,fg='red').pack(side=LEFT, padx=2, pady=2)

        f2=Frame(self)
        f2.pack(fill=X)
        ALiUrlFrame(f2).pack(side=LEFT,fill=X)
    
    def getcount(self):
        while True:
            try:
                rs1=self.ms.ExecQuery('select count(*) from adtask where sjbm=? and state=0',(self.sjbm,))
                if rs1[0][0]==0:
                    self.btn_cjsku.config(state=NORMAL)
                    self.statusStr.set(f'商家编码:《{self.sjbm}》,采集SKU任务未开启')

                else:
                    self.btn_cjsku.config(state=DISABLED)
                    self.statusStr.set(f'商家编码:《{self.sjbm}》任务执行中,剩余页面:{rs1[0][0]}')
                
                time.sleep(5)
            except Exception as e:
                pass
    
    def cat_skutask(self):

        sjbm=self.SJBMIpt.get().strip()
        if not sjbm:
            messagebox.showinfo('提示','请先输入商家编码')
            return
        self.btn_catstatus.config(state=DISABLED)
        self.sjbm=sjbm
        #先查询商家编码是否合法
        print('正在校准商家码...')
        self.statusStr.set('正在校准商家码...')
        rs=trydosql(self.ms,'select count(*) from adpro where sjbm=?',(sjbm,))
        if rs[0][0]==0:
            print(f'无效商家编码:{sjbm}')
            self.statusStr.set(f'无效商家编码:{sjbm}')
            messagebox.showinfo('提示',f'无效商家编码:{sjbm}')
            self.btn_catstatus.config(state=NORMAL)
            return

        #查询该商家编码是否在任务中
        print('开始查询是否商家在任务中...')
        rs=self.ms.ExecQuery('select count(*) from adtask where sjbm=? and state=0',(sjbm,))
        if rs[0][0]>0:
            print(f'商家编码:《{sjbm}》,正在执行采集任务,任务剩余页面:{rs[0][0]}')

        else:

            print(f'商家编码:《{sjbm}》,采集任务未开启,可发布任务')
            self.btn_cjsku.config(state=NORMAL)

        if not self.t0.is_alive():
            self.t0.start()
            print(f'检测开启')
    
        
        self.btn_catstatus.config(state=NORMAL)

    def send_task(self):
        if not messagebox.askyesno('提示','确定发布采集SKU任务'):
            return
        self.btn_cjsku.config(state=DISABLED)
        sqlstr='''select shopid,ProductID,dpurl,sjbm,sjname,yyname,ProductNameAdName from adpro 
                    where id in(select min(id) from adpro where sjbm=? group by productid)'''
        
        rs=self.ms.ExecQuerydict(sqlstr,(self.sjbm))
        print(f'待发布任务:{len(rs)}')
        self.send_count=len(rs)
        sufb=0
        sbfb=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=1
            for r in rs:

                futures.append(executor.submit(self.upload_task,row,r))
                row+=1
            for future in concurrent.futures.as_completed(futures):
                try:
                    res_status=future.result()
                    if res_status:
                        sufb+=1
                    else:
                        sbfb+=1
                except Exception as exc:
                    print(f"线程出错: {exc}")
        
        print(f'发布完成,共{self.send_count}个任务,其中成功发布:{sufb}个,失败{sbfb}个')
        messagebox.showinfo('提示',f'发布完成,共{len(rs)}个任务,其中成功发布:{sufb}个,失败{sbfb}个')

    def upload_task(self,row,pitem):
        pid=pitem['ProductID']
        self.send_count-=1
        rs=self.ms.ExecQuery('select count(*) from adtask where ProductID=?',(pid,))
        if rs[0][0]>0:
            aff=self.ms.ExecNoQuery('update adtask set state=0 where ProductID=?',(pid,))

            if aff>0:
                print(f'第{row}个,pid:{pid},已存在,将state状态变0成功,发布成功,剩余{self.send_count}个未发布')
                return 1
            else:
                print(f'第{row}个,pid:{pid},已存在,将state状态变0失败,发布失败,剩余{self.send_count}个未发布')
                return 0
        else:
            params=list(pitem.values())
            zdstr=','.join([k for k in pitem.keys()])
            wstr=','.join(['?' for _ in params])

            aff=self.ms.ExecNoQuery(f'insert into adtask({zdstr}) values ({wstr})',params)
            if aff>0:
                print(f'第{row}个,pid:{pid},不存在,新增成功,发布成功,剩余{self.send_count}个未发布')
                return 1
            else:
                print(f'第{row}个,pid:{pid},不存在,新增失败,发布失败,剩余{self.send_count}个未发布')
                return 0

class ALiUrlFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.hyCount=IntVar()
        self.SkuCount=IntVar()
        self.rowCount=IntVar()
        self.createWidgets()

    def createWidgets(self):

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X)

        Label(f00, text='商家编码:').pack(side=LEFT, padx=2, pady=2)
        self.SJBMIpt = Entry(f00,width=10)
        self.SJBMIpt.pack(side=LEFT,padx=2, pady=2)
        #Thread(target=self.getcount).start()
        self.btn_dcAliurl=Button(f00,text='导出1688货源链接',command=lambda:Thread(target=self.dc_aliurl).start(),fg='#ffffff',bg='green')
        self.btn_dcAliurl.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.hyCount,fg='red').pack(side=LEFT, padx=2, pady=2)
        self.btn_dcSku=Button(f00,text='导出印尼库存表',command=lambda:Thread(target=self.dc_sku).start(),fg='#ffffff',bg='green')
        self.btn_dcSku.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.SkuCount,fg='red').pack(side=LEFT, padx=2, pady=2)

        Label(f00, text='库存表路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.rowCount,fg='red')
        Button(f00, text='选择文件', command=self.choosefile).pack(side=LEFT, padx=2, pady=2)
        self.btn_drSku=Button(f00,text='上传印尼库存表',command=lambda:Thread(target=self.up_sku).start(),fg='#ffffff',bg='green')
        self.btn_drSku.pack(side=LEFT, padx=2, pady=2)
    
    def getcount(self):
        while True:
            if self.SJBMIpt.get().strip():
                self.sjbm=self.SJBMIpt.get().strip()
                try:
                    rs1=self.ms.ExecQuery('''select COUNT(DISTINCT aliurl) from ProShopeePh where  SJBM=? and aliurl is not null  and aliurl  
                                            not in(select aliurl from YNSkuStock where aliurl is not null and SJBM=? )  
                                        ''',(self.sjbm,self.sjbm,))
                    rs2=self.ms.ExecQuery('select count(DISTINCT purl) from ynskustock where sjbm=?',(self.sjbm,))
                    self.hyCount.set(rs1[0][0])
                    self.SkuCount.set(rs2[0][0])
                    
                    
                except Exception as e:
                    pass
            
                time.sleep(5)
     
    def choosefile(self):

        select_path = filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0, select_path)
            self.fpath = select_path
            wb = openpyxl.load_workbook(select_path)
            ws = wb[wb.sheetnames[0]]
            max_row = ws.max_row
            self.rowCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示', '请选择excel文件')
    
    def dc_aliurl(self):

        sjbm=self.SJBMIpt.get().strip()
        if not sjbm:
            messagebox.showinfo('提示','请先输入商家编码')
            return
        self.btn_dcAliurl.config(state=DISABLED)
        sqlstr='''select aliurl [1688货源链接],yntitle [印尼标题] from ProShopeePh
                    where id in( select min(id) from ProShopeePh
                    where  SJBM=? 
                    and aliurl is not null  
                    and aliurl not in(select aliurl from YNSkuStock where aliurl is not null and SJBM=?)
                    group by aliurl)'''

        fqz=f'{sjbm}_1688货源链接'
        fname=sqlToExcel(fqz,self.ms,sqlstr,(sjbm,sjbm))

        if fname:
            print(f'成功导出:{fname}')
            messagebox.showinfo('提示',f'成功导出:{fname}')
        else:
            print(f'无效导出:{sjbm}')
            messagebox.showinfo('提示',f'无效导出:{sjbm}')
        self.btn_dcAliurl.config(state=NORMAL)
    
    def dc_sku(self):

        sjbm=self.SJBMIpt.get().strip()
        if not sjbm:
            messagebox.showinfo('提示','请先输入商家编码')
            return
        self.btn_dcSku.config(state=DISABLED)
        sqlstr='''
                    select sjbm,dpurl,purl,ProductNameAdName,saleprice,aliurl from ynskustock
                    where [id] in (select min(id) from ynskustock where sjbm=? group by purl) order by aliurl desc
                '''

        fqz=f'{sjbm}_印尼库存表'
        fname=sqlToExcel(fqz,self.ms,sqlstr,(sjbm,))

        if fname:
            print(f'成功导出:{fname}')
            messagebox.showinfo('提示',f'成功导出:{fname}')
        else:
            print(f'无效导出:{sjbm}')
            messagebox.showinfo('提示',f'无效导出:{sjbm}')
        self.btn_dcSku.config(state=NORMAL)

    def up_sku(self):

        def upsku(crow,pitem:dict):
            cpurl=pitem.pop('purl')
            params=list(pitem.values())
            zdstr=','.join([f'{k}=?' for k in pitem.keys()])
            wstr=','.join(['?' for _ in params])

            sqlstr=f'''update ynskustock set {zdstr} where purl=?'''

            aff=self.ms.ExecNoQuery(sqlstr,params+[cpurl])

            return crow,cpurl,aff

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入文件')
            return
        
        self.btn_drSku.config(state=DISABLED)
        pdatas=getDictDatasByEecel2(self.fpath)
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=2
            for pdata in pdatas:
                futures.append(executor.submit(upsku,row,pdata))
                row+=1

            for future in concurrent.futures.as_completed(futures):
                try:
                    res_row,res_purl,res_aff = future.result()
                    print(f'第 {res_row} 行,purl:{res_purl},更新sku数:{res_aff}')
                except Exception as exc:
                    print(f"线程出错: {exc}")
        print(f'全部上传完毕')
        messagebox.showinfo('提示','全部上传完毕')
        self.btn_drSku.config(state=NORMAL)

class PiePeiFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.root=master
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statusStr=StringVar()
        self.statusStr.set('正在获取产品数量...')
        self.createWidgets()
        Thread(target=self.getcount).start()

    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='数据匹配模版', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,side=LEFT,padx=2,pady=2)
        f001=Frame(f00)
        f001.pack(fill=X)
        Label(f001, textvariable=self.statusStr,fg='red').pack(side=LEFT, padx=2, pady=2)
        f000=Frame(f00)
        f000.pack(fill=X)
        Label(f000, text='模版ID:').pack(side=LEFT, padx=2, pady=2)
        self.MbIDIpt = Entry(f000,width=5)
        self.MbIDIpt.pack(side=LEFT,padx=2, pady=2)
        Label(f000, text='关键词:').pack(side=LEFT, padx=2, pady=2)
        self.KWIpt = Entry(f000,width=10)
        self.KWIpt.pack(side=LEFT,padx=2, pady=2)
        Label(f000, text='货币单位:').pack(side=LEFT, padx=2, pady=2)
        self.HBIpt = Entry(f000,width=5)
        self.HBIpt.pack(side=LEFT,padx=2, pady=2)
        self.HBIpt.insert(0,'usd')
        self.btn_pp=Button(f000,text='开始匹配',command=lambda:Thread(target=self.start_pp).start(),fg='#ffffff',bg='green')
        self.btn_pp.pack(side=LEFT, padx=2, pady=2)
    
    def getcount(self):
        time.sleep(20)
        while True:
   
            try:
                hbstr=self.HBIpt.get().strip()
                if hbstr:

                    rs1=self.ms.ExecQuery('select count(distinct itemid) from proshopeeph where currency=?',(hbstr,))
                    rs2=self.ms.ExecQuery('select count(distinct itemid) from proshopeeph where currency=? and pmbid is null',(hbstr,))
                    self.statusStr.set(f'货币:{hbstr},共有产品数量:{rs1[0][0]}个,未匹配过数据:{rs2[0][0]}')

            except Exception as e:
                    pass
            
            time.sleep(5)
        
    def start_pp(self):
        kw=self.KWIpt.get().strip()
        mbid=self.MbIDIpt.get().strip()
        hbstr=self.HBIpt.get().strip()
        if not kw or not mbid or not hbstr:
            messagebox.showinfo('提示','请先先输入信息...')
            return
        if not messagebox.askyesno('提示','确定开启匹配?'):
            return
        self.btn_pp.config(state=DISABLED)
        print(f'关键词:{kw},mbid:{mbid}开始匹配...')
        
        sqlstr=f'''
            INSERT INTO proshopeeph_pmbid (itemid, pmbid)
            SELECT itemid, {mbid} as pmbid
            FROM proshopeeph
            WHERE id IN (
                SELECT MIN(id)
                FROM proshopeeph
                WHERE currency = ?
                AND pmbid IS NULL
                AND dbo.MatchExactKeywords(?, pname) = 1 
                GROUP BY itemid
            );
            '''
        
        aff=self.ms.ExecNoQuery(sqlstr,(hbstr,kw))

        sqlstr2='''UPDATE proshopeeph
                    SET pmbid = CASE 
                                    WHEN pmbid IS NULL THEN 1
                                    ELSE pmbid + 1
                                END
                    WHERE itemid IN (
                        SELECT itemid
                        FROM proshopeeph
                        WHERE currency = ?
                        AND pmbid IS NULL
                        AND dbo.MatchExactKeywords(?, pname) = 1
                        GROUP BY itemid
                    );
                '''
        aff2=self.ms.ExecNoQuery(sqlstr2,(hbstr,kw))
        print(f'匹配完毕,模版id:{mbid},关键词:《{kw}》,成功匹配{aff}个,标记成功{aff2}个')
        messagebox.showinfo('提示',f'匹配完毕,模版id:{mbid},关键词:《{kw}》,成功匹配{aff}个')
        self.btn_pp.config(state=NORMAL)

# import tkinter
# list_win = tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('主页')
# HomeFrame(list_win).pack(fill=X, padx=3, pady=3)
# list_win.mainloop()